package branchws;

import accountws.IAccountDB;
import accountws.LoginReply;
import accountws.User;
import bookdbws.Author;
import bookdbws.Book;
import bookdbws.IBookDB;
import bookdbws.Publisher;
import hqclient.BranchWebServiceWS_Service;
import hqclient.Response;
import java.security.SecureRandom;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.xml.ws.WebServiceRef;

/**
 *
 * @author Atuan
 */
public class DBHandler implements IAccountDB,IBranchDB,IBookDB, IBranchHQDB {
    
    private String generateSessionID(String uname){
        String ans="";
        SecureRandom sr = new SecureRandom();
        for(int i = 0; i < 24; i++){
            ans += sr.nextInt(10);
        }        
        System.out.println("sessionID generated: "+ans+uname);
        return ans+uname;
    }
    
    //MAIN
    @Override
    public Boolean checkUser(String sid){
        Connection con;
        Boolean ans = false;
        try {
        con = DriverManager.getConnection(
            "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
        con.setAutoCommit(false);
        PreparedStatement sta = con.prepareStatement(
                "SELECT SESSION_EXP FROM USERS WHERE SESSION_ID = ?");
        sta.setString(1, sid);
        ResultSet res = sta.executeQuery();
        
        if(res.next()){
            Timestamp t = new Timestamp(System.currentTimeMillis());
            if(t.before(res.getTimestamp("SESSION_EXP")))
            {
                updateTimestamp(con,sid,new Timestamp(System.currentTimeMillis()+1800000));
                ans = true;
            } 
        }
        res.close();
        sta.close();
        con.commit();
        con.close();
        return ans;
        } catch (Exception e) {
            System.err.println("ExceptionCheckUser: "+e.getMessage());
            return false;
        }
    }
    
    private Boolean updateTimestamp(Connection con, String sid, Timestamp t){
        try {
        PreparedStatement sta = con.prepareStatement(
                "UPDATE USERS SET SESSION_EXP = ? WHERE SESSION_ID = ?");
        sta.setTimestamp(1, t);
        sta.setString(2, sid);        
        sta.execute();        
        sta.close();       
        return true;
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return false;
        }
    }
    
    private Boolean updateSessionFields(Connection con, String uname, String sessionid, Timestamp t){
        try {
        PreparedStatement sta = con.prepareStatement(
                "UPDATE USERS SET SESSION_ID = ?, SESSION_EXP = ? WHERE USERNAME = ?");
        sta.setString(1, sessionid);
        sta.setTimestamp(2, t);
        sta.setString(3, uname);        
        sta.execute();        
        sta.close();         
        return true;
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return false;
        }
    }
    
    //ACCOUNTWS
    @Override
    public Boolean addUser(String uname,String pass){
        return addUser(uname,pass,"user");
    }
    
    @Override
    public Boolean addUser(String uname,String pass,String role){
        Connection con;
        try {
        con = DriverManager.getConnection(
            "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
        con.setAutoCommit(false);
        PreparedStatement sta = con.prepareStatement(
                "INSERT INTO USERS (USERNAME, PASSWORD, ROLE) VALUES ( ?, ?, ?)");
        sta.setString(1, uname);
        sta.setString(2, pass);
        sta.setString(3, role);
        sta.executeUpdate();
        
        sta.close();
        con.commit();
        con.close(); 
        return true;
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return false;
        }
    }
    
    @Override
    public Boolean removeUser(String uname){
        Connection con;
        try {
        con = DriverManager.getConnection(
            "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
        con.setAutoCommit(false);
        PreparedStatement sta = con.prepareStatement(
                "DELETE FROM USERS WHERE USERNAME = ? ");
        sta.setString(1, uname);        
        sta.execute();        
        sta.close();
        con.commit();
        con.close();          
        return true;
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return false;
        }
    }
    
    @Override
    public List<User> getAllUsers(){
        Connection con;
        List<User> users = new ArrayList<User>();
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM USERS");

            ResultSet res = sta.executeQuery();
            while (res.next()) {
                users.add(new User(
                        res.getString("USERNAME"),
                        res.getString("PASSWORD"),
                        res.getString("ROLE")));
            }
            res.close();
            sta.close();
            con.commit();
            con.close();   
            return users;
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return null;
        }
    }
    
    @Override
    public LoginReply login(String uname, String pass){
        Connection con;
        String ans;
        String sid="";
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                "SELECT ROLE FROM USERS WHERE USERNAME = ? AND PASSWORD = ? ");
            sta.setString(1,uname);
            sta.setString(2,pass);
            ResultSet res = sta.executeQuery();
                        
            if(res.next()){
                ans = res.getString("ROLE");
                sid = generateSessionID(uname);
                updateSessionFields(con, uname, sid, new Timestamp(System.currentTimeMillis()+1800000));
                processOrders();
            } else {
                ans = "loginfailed";               
            }
            System.out.println("login ans: "+ans);
            res.close();
            sta.close();
            con.commit();
            con.close();
            return new LoginReply(sid,ans);
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return new LoginReply("","loginfailed");
        }
    }
    
    @Override
    public Boolean logout(String uname){        
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                "UPDATE USERS SET SESSION_ID = NULL, SESSION_EXP = NULL WHERE USERNAME = ?");
            sta.setString(1,uname);
            sta.execute();             
            sta.close();
            con.commit();
            con.close();
            return true;
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return false;
        }
    }
    
    //BOOKDBWS
    
    private Boolean checkForBook(Connection con, int isbn){
        Boolean ans = false;
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKS WHERE ISBN = ?");            
            sta.setInt(1, isbn);
            ResultSet res = sta.executeQuery();
            if(res.next()){
                ans = true;
            }         
            res.close();
            sta.close();
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionCheckForBook: "+e.getMessage());
            return false;
        }
    }
        
    private int getAuthorIndex(Connection con, Author author){
        int ans = -1;
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM AUTHORS WHERE NAME = ? AND SURNAME = ?");            
            sta.setString(1, author.getName());
            sta.setString(2, author.getSurname());
            ResultSet res = sta.executeQuery();
            if(res.next()){
                ans = res.getInt("ID");
            }         
            res.close();
            sta.close();
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionGetAuthorIndex: "+e.getMessage());
            return -1;
        }
    }
    
    private int getPublishersIndex(Connection con, String name){
        int ans = -1;
        try{
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM PUBLISHERS WHERE NAME = ?");
            sta.setString(1, name);
            ResultSet res = sta.executeQuery();
            if(res.next()){
                ans = res.getInt("ID");
            }
            res.close();
            sta.close();
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptiongetPublishersIndex: "+e.getMessage());
            return -1;
        }
    }
    
    private int addPublisher(Connection con, Publisher p){
        try{            
            int ans = getPublishersIndex(con, p.getName());
            System.out.println("Pubname: "+p.getName()+" "+ans);
            if(ans == -1)
            {
                PreparedStatement sta = con.prepareStatement(
                    "INSERT INTO PUBLISHERS (NAME) VALUES (?)");
                sta.setString(1, p.getName());
                sta.execute();
                sta.close(); 
                ans = getPublishersIndex(con, p.getName());
                System.out.println("Pubname: "+p.getName()+" "+ans);
            }
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionAddPublisher: "+e.getMessage());
            return -1;
        }
    }

    @Override
    public Boolean addBook(Book book){
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            int pub_id = addPublisher(con, book.getPublisher());
            PreparedStatement sta = con.prepareStatement(
                    "INSERT INTO BOOKS (TITLE, PUBLISHER_ID, ISBN, PRICE, AMOUNT) VALUES (?, ?, ?, ?, ?)");            
            sta.setString(1, book.getTitle());
            sta.setInt(2, pub_id);
            sta.setString(3, book.getIsbn());
            sta.setDouble(4, book.getPrice());
            sta.setInt(5, book.getAmount());
            sta.execute();
            sta.close();
            
            if(!addAuthors(con, book.getAuthors(), book.getIsbn())){
                removeBook(book.getIsbn());
                con.close();
                return false;
            }        
            con.commit();
            con.close();             
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionAddBook: "+e.getMessage());
            return false;
        }
    }
    private List<Integer> getBookAuthorsID(Connection con, String isbn){
        List<Integer> ans = new ArrayList<Integer>();
        try{
            PreparedStatement sta = con.prepareStatement(
                    "select * from bookauthor where book_isbn = ?");
            sta.setString(1, isbn);
            ResultSet res = sta.executeQuery();
            while(res.next()){
                ans.add(res.getInt("AUTHOR_ID"));
            }
            res.close();
            sta.close();
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionGetBookAuthorRel: "+e.getMessage());
            return ans;
        }        
    }
    
    private int getBookAuthorRelationIndex(Connection con, int author_id, String isbn){
        int ans = -1;
        try{
            PreparedStatement sta = con.prepareStatement(
                    "select * from bookauthor where author_id = ? and book_isbn = ?");
            sta.setInt(1, author_id);
            sta.setString(2, isbn);
            ResultSet res = sta.executeQuery();
            if(res.next()){
                ans = res.getInt("ID");
            }
            res.close();
            sta.close();
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionGetBookAuthorRel: "+e.getMessage());
            return -1;
        }        
    }

    private Boolean addAuthors(Connection con, List<Author> authors, String isbn){
        try {
            PreparedStatement sta=null;          
            for(int i = 0; i < authors.size(); i++)
            {  
                int index = getAuthorIndex(con,authors.get(i));
                System.out.println("ADDAUTHORIndex: "+index);
                if(index == -1){
                    sta = con.prepareStatement(
                        "INSERT INTO AUTHORS (NAME, SURNAME) VALUES (?, ?)");
                    sta.setString(1, authors.get(i).getName());
                    sta.setString(2, authors.get(i).getSurname());
                    sta.execute();
                    index = getAuthorIndex(con,authors.get(i));
                }
                int abindex = getBookAuthorRelationIndex(con,index,isbn);
                System.out.println("ADDAUTHORABIndex: "+abindex);
                if(abindex == -1){
                    sta = con.prepareStatement(
                            "INSERT INTO BOOKAUTHOR (AUTHOR_ID, BOOK_ISBN) VALUES (?, ?)");
                    sta.setInt(1, index);
                    sta.setString(2, isbn);
                    sta.execute();
                }
            }            
            sta.close();
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionAddAuthor: "+e.getMessage());
            return false;
        }
    }
    
    private Publisher getPublisher(Connection con, int pubid){
        String ans = "";
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM PUBLISHERS WHERE ID = ?");            
            sta.setInt(1, pubid);
            ResultSet res = sta.executeQuery();
            if(res.next()){
                ans = res.getString("NAME");
            }         
            res.close();
            sta.close();
            if(!ans.isEmpty()){
                return new Publisher(pubid,ans);
            } else {
                return null;
            }
        } catch (Exception e) {
            System.err.println("ExceptionGetPublisher: "+e.getMessage());
            return null;
        }
    }
    
    private List<Author> getBookAuthors(Connection con, String isbn){
        List<Author> result = new ArrayList<Author>();
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKAUTHOR WHERE BOOK_ISBN = ?");            
            sta.setString(1, isbn);
            ResultSet res = sta.executeQuery();
            int index;
            while(res.next())
            {
                index = res.getInt("AUTHOR_ID");
                if(index != 0)
                {
                    System.out.println("GBAIndex: "+index);
                    Author a = getAuthor(con,index);
                    if(a != null){
                        result.add(getAuthor(con,index));                        
                    }
//                    PreparedStatement sta1 = con.prepareStatement(
//                        "SELECT * FROM AUTHORS WHERE ID = ?");
//                    sta1.setInt(1, index);
//                    ResultSet res1 = sta1.executeQuery();
//                    result.add(new Author(
//                            index,
//                            res1.getString("NAME"),
//                            res1.getString("SURNAME")
//                            ));
//                    System.out.println("GBAAuthors: "+res1.getString("NAME")
//                            +" "+res1.getString("SURNAME"));
//                    res1.close();
//                    sta1.close();
                }
            }         
            res.close();
            sta.close();
            return result;
        } catch (Exception e) {
            System.err.println("ExceptionGetBookAuthors: "+e.getMessage());
            return result;
        }
    }
    
    private Author getAuthor(Connection con, int id){
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM AUTHORS WHERE ID = ?");
            sta.setInt(1, id);
            ResultSet res = sta.executeQuery();
            Author a = null;
            if(res.next()){
                a = new Author(
                        id,
                        res.getString("NAME"),
                        res.getString("SURNAME")
                        );
            }
            res.close();
            sta.close();
            return a;
        } catch (Exception e) {
            System.err.println("ExceptionGetAuthor: "+e.getMessage());
            return null;
        }
    }
    
    @Override
    public Book getBook(String isbn){
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKS WHERE ISBN = ?");            
            sta.setString(1, isbn);            
            ResultSet res = sta.executeQuery();
            Book book = null;            
            if(res.next()){
                book = new Book(
                        res.getString("TITLE"),
                        isbn,
                        getPublisher(con,res.getInt("PUBLISHER_ID")),
                        getBookAuthors(con,isbn),
                        res.getInt("AMOUNT"),
                        res.getDouble("PRICE")
                        );
            }
            res.close();            
            sta.close();
            con.commit();
            con.close();
            return book;
        } catch (Exception e) {
            System.err.println("ExceptionGetBook: "+e.getMessage());
            return null;
        }
    }
    
    @Override
    public List<Book> searchForBooksByTitle(String title){
        List<Book> books = new ArrayList<Book>();
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");            
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKS WHERE TITLE = ?");            
            sta.setString(1, title);            
            ResultSet res = sta.executeQuery();            
            while(res.next()){
                String isbn = res.getString("ISBN");
                books.add(new Book(
                        title,
                        isbn,
                        getPublisher(con,res.getInt("PUBLISHER_ID")),
                        getBookAuthors(con,isbn),
                        res.getInt("AMOUNT"),
                        res.getDouble("PRICE")
                        ));
            }
            res.close();            
            sta.close();
            con.commit();
            con.close();
        } catch (Exception e) {
            System.err.println("ExceptionSearchTitle: "+e.getMessage());
        }                
        return books;
    }
    
    @Override
    public List<Book> searchForBooksByPublisher(String name){
        List<Book> books = new ArrayList<Book>();
        Connection con;
        try{
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");            
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM PUBLISHERS WHERE NAME = ?");   
            sta.setString(1, name);
            ResultSet res = sta.executeQuery();
            if(res.next())
            {
                int pubid = res.getInt("ID");
                
                sta = con.prepareStatement(
                        "SELECT * FROM BOOKS WHERE PUBLISHER_ID = ?");            
                sta.setInt(1, pubid);            
                res = sta.executeQuery();            
                while(res.next()){
                    String isbn = res.getString("ISBN");
                    books.add(new Book(
                            res.getString("TITLE"),
                            isbn,
                            getPublisher(con,pubid),
                            getBookAuthors(con,isbn),
                            res.getInt("AMOUNT"),
                            res.getDouble("PRICE")
                            ));
                }
            }
            res.close();            
            sta.close();
            con.commit();
            con.close();
        } catch (Exception e) {
            System.err.println("ExceptionSearchPublisher: "+e.getMessage());
        }    
        return books;
    }
    
    @Override
    public List<Book> searchForBooksByAuthor(String name, String surname){
        List<Book> books = new ArrayList<Book>();
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");            
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM AUTHORS WHERE NAME = ? AND SURNAME = ?");   
            sta.setString(1, name);
            sta.setString(2, surname);
            ResultSet res = sta.executeQuery();
            if(res.next())
            {
                int autid = res.getInt("ID");
                sta = con.prepareStatement(
                        "SELECT * FROM BOOKAUTHOR WHERE AUTHOR_ID = ?");            
                sta.setInt(1, autid);            
                res = sta.executeQuery();            
                while(res.next()){
                    String isbn = res.getString("BOOK_ISBN");
                    books.add(getBook(isbn));
                }
            }
            res.close();            
            sta.close();
            con.commit();
            con.close();
        } catch (Exception e) {
            System.err.println("ExceptionSearchAuthor: "+e.getMessage());
        }      
        return books;
    }
    
    @Override
    public List<Book> searchForBooksByAuthor(String surname){
        List<Book> books = new ArrayList<Book>();
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");            
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM AUTHORS WHERE SURNAME = ?");   
            sta.setString(1, surname);
            ResultSet res = sta.executeQuery();
            while(res.next())
            {
                int autid = res.getInt("ID");
                sta = con.prepareStatement(
                        "SELECT * FROM BOOKAUTHOR WHERE AUTHOR_ID = ?");            
                sta.setInt(1, autid);            
                ResultSet res1 = sta.executeQuery();            
                while(res1.next()){
                    String isbn = res1.getString("BOOK_ISBN");
                    books.add(getBook(isbn));
                }
                res1.close();
            }
            res.close();            
            sta.close();
            con.commit();
            con.close();
        } catch (Exception e) {
            System.err.println("ExceptionSearchAuthor: "+e.getMessage());
        }      
        return books;
    }

    private int getNumberOfBooks(Connection con, String isbn){
        int ans  = 0;
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKS WHERE ISBN = ?");
            sta.setString(1, isbn);        
            ResultSet res = sta.executeQuery();   
            if(res.next()){
                ans = res.getInt("AMOUNT");
            }
            res.close();
            sta.close();           
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveBook: "+e.getMessage());
            return ans;
        }
    }
    
    private int getNumberOfPublishersBooks(Connection con, int id){
        int ans  = 0;
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT COUNT(PUBLISHER_ID) AS PUBID FROM BOOKS WHERE PUBLISHER_ID = ?");
            sta.setInt(1, id);        
            ResultSet res = sta.executeQuery();   
            if(res.next()){
                ans = res.getInt("PUBID");
            }
            res.close();
            sta.close();           
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveBook: "+e.getMessage());
            return ans;
        }
    }
    
    private int getNumberOfAuthorsBooks(Connection con, int id){
        int ans  = 0;
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKAUTHOR WHERE AUTHOR_ID = ?");
            sta.setInt(1, id);        
            ResultSet res = sta.executeQuery();   
            while(res.next()){
                ans++;
            }
            res.close();
            sta.close();           
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveBook: "+e.getMessage());
            return ans;
        }
    }
    
    @Override
    public Boolean removeCopyOfBook(String isbn){
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = null;
            int num = getNumberOfBooks(con,isbn);            
            System.out.println("NUmber of copies of the book: "+num);
            if(num > 1){
                sta = con.prepareStatement(
                        "UPDATE BOOKS SET AMOUNT = ? WHERE ISBN = ?");
                sta.setInt(1, num-1);
                sta.setString(2, isbn);        
                sta.execute();        
            } else {
                sta = con.prepareStatement(
                        "DELETE FROM BOOKS WHERE ISBN = ?");
                sta.setString(1, isbn);        
                sta.execute();  
                List<Integer> authids = this.getBookAuthorsID(con, isbn);
                for(int ii=0; ii<authids.size();ii++)
                {                    
                    sta = con.prepareStatement(
                            "DELETE FROM BOOKAUTHOR WHERE BOOK_ISBN = ?");
                    sta.setString(1, isbn);        
                    sta.execute(); 
                        
                    System.out.println("getNumberOfAuthorsBooks:"+ii+" "+getNumberOfAuthorsBooks(con, authids.get(ii)));
                    if(getNumberOfAuthorsBooks(con, authids.get(ii)) < 1)
                    {
                        sta = con.prepareStatement(
                                "DELETE FROM AUTHORS WHERE ID = ?");
                        sta.setInt(1, authids.get(ii));        
                        sta.execute(); 
                    }
                }
                int pid = getPublishersID(con,isbn);
                int npid = getNumberOfPublishersBooks(con, 
                        pid);
                
                System.out.println("pid+npid: "+pid+" "+npid);
                if(npid < 1){
                    sta = con.prepareStatement(
                                "DELETE FROM PUBLISHERS WHERE ID = ?");
                        sta.setInt(1, npid);        
                        sta.execute();
                }
            }
            sta.close();
            con.commit();
            con.close();           
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveCopyOfBook: "+e.getMessage());
            return false;
        }
    }
    
    private int getPublishersID(Connection con, String isbn){
        int ans = 0;
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKS WHERE ISBN = ?");
            sta.setString(1, isbn);        
            ResultSet res = sta.executeQuery();   
            while(res.next()){
                ans++;
            }
            res.close();
            sta.close();           
            return ans;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveBook: "+e.getMessage());
            return ans;
        }
    }
    
    @Override
    public Boolean removeBook(String isbn){
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "DELETE FROM BOOKS WHERE ISBN = ?");
            sta.setString(1, isbn);             
                sta.execute();  
                List<Integer> authids = this.getBookAuthorsID(con, isbn);
                for(int ii=0; ii<authids.size();ii++)
                {                    
                    sta = con.prepareStatement(
                            "DELETE FROM BOOKAUTHOR WHERE BOOK_ISBN = ?");
                    sta.setString(1, isbn);        
                    sta.execute(); 
                        
                    System.out.println("getNumberOfAuthorsBooks:"+ii+" "+getNumberOfAuthorsBooks(con, authids.get(ii)));
                    if(getNumberOfAuthorsBooks(con, authids.get(ii)) < 1)
                    {
                        sta = con.prepareStatement(
                                "DELETE FROM AUTHORS WHERE ID = ?");
                        sta.setInt(1, authids.get(ii));        
                        sta.execute(); 
                    }
                }
                int pid = this.getPublishersID(con,isbn);
                int npid = this.getNumberOfPublishersBooks(con, 
                        pid);
                
                System.out.println("pid+npid: "+pid+" "+npid);
                if(npid == 1){
                    sta = con.prepareStatement(
                                "DELETE FROM PUBLISHERS WHERE ID = ?");
                        sta.setInt(1, npid);        
                        sta.execute();
                }
            sta.close();
            con.commit();
            con.close();           
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveBook: "+e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    private Boolean removeBookAuthor(Connection con, int aid, String isbn){
        try {
            PreparedStatement sta = con.prepareStatement(
                    "DELETE FROM BOOKAUTHOR WHERE AUTHOR_ID = ? AND BOOK_ISBN = ?");
            sta.setInt(1, aid);        
            sta.setString(2,isbn);
            sta.execute();        
            sta.close();          
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveBookAuthor: "+e.getMessage());
            return false;
        }
    }
    
    private Boolean updatePublisher(Connection con,Publisher pub, String isbn){
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKS WHERE ISBN = ?"); 
            sta.setString(1, isbn);
            ResultSet res = sta.executeQuery();
            if(res.next()){
                int id = res.getInt("PUBLISHER_ID");
                System.out.println("UPid: "+id);
                sta = con.prepareStatement(
                        "SELECT * FROM PUBLISHERS WHERE ID = ?"); 
                sta.setInt(1, id);
                ResultSet res1 = sta.executeQuery();
                if(res1.next()){
                System.out.println("UP2if: ");
                    if(!res1.getString("NAME").equals(pub.getName())){
                System.out.println("UP3if: "+res1.getString("NAME"));
                        //check if it's the only one book
                        //yes - update
                        //no - add
                        int numb = getNumberOfPublishersBooks(con, id);
                        if(numb == 1){
                            //if exists leave
                            System.out.println("UP4if: update"+numb);
                            if(getPublishersIndex(con, pub.getName()) < 1){
                                //else update
                                sta = con.prepareStatement(
                                    "UPDATE PUBLISHERS SET NAME = ? WHERE ID = ?"); 
                                sta.setString(1, pub.getName());
                                sta.setInt(2, id);
                                sta.execute();
                            }
                        } else {
                            System.out.println("UP4if: add"+numb);
                            if(getPublishersIndex(con, pub.getName()) < 1){
                                sta = con.prepareStatement(
                                    "INSERT INTO PUBLISHERS (NAME) VALUES(?)"); 
                                sta.setString(1, pub.getName());
                                sta.execute();              
                            }
                        }
                    }
                }
                res1.close();
            }
            res.close();
            sta.close();
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionUpdateAuthor: "+e.getMessage());
            return false;
        }
    }
    
    private Boolean updateAuthors(Connection con,List<Author> authors, String isbn){
        try {
        //getcurrent authorsID
        PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKAUTHOR WHERE BOOK_ISBN = ?");  
            sta.setString(1, isbn);
            ResultSet res = sta.executeQuery();
        //foreach of them
            while(res.next())
            {
                int i = 0;
                int ind = res.getInt("AUTHOR_ID");
                while(i < authors.size())
                {
        //if contained in new authors list delete from authorslist and leave
                    if(authors.get(i).getId() == ind || authors.equals(getAuthor(con,ind)))
                    {
                        authors.remove(i);
                        i = authors.size()+10;
                    }
                    else
                    {
                        i++;
                    }
                }
        //if different then
        //delete record from bookauthor and
                if(i != authors.size()+10)
                {
                    removeBookAuthor(con,ind,isbn);
        //if it was the only one book from the author
        //then delete the author
                    if(getNumberOfAuthorsBooks(con, ind) < 1)
                    {
                        sta = con.prepareStatement(
                            "DELETE FROM AUTHORS WHERE ID = ?");
                        sta.setInt(1, ind);
                        sta.execute();
                    }
                }
            }
        //the rest of the authors from the list:
        //if already in the db then leave
        //if not then add
            for(int i = 0; i < authors.size(); i++)
            {
                int index = getAuthorIndex(con,authors.get(i));
                if(index < 1)
                {                    
                    sta = con.prepareStatement(
                        "INSERT INTO AUTHORS (NAME, SURNAME) VALUES (?, ?)"); 
                    sta.setString(1, authors.get(i).getName());
                    sta.setString(2, authors.get(i).getSurname());
                    sta.execute();
                }
                index = getAuthorIndex(con,authors.get(i));
                int abindex = getBookAuthorRelationIndex(con,index,isbn);
                if(abindex < 1)
                {
                    sta = con.prepareStatement(
                            "INSERT INTO BOOKAUTHOR (AUTHOR_ID, BOOK_ISBN) VALUES (?, ?)");
                    sta.setInt(1, index);
                    sta.setString(2, isbn);
                    sta.execute();
                }
            }
            sta.close();
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionUpdateAuthor: "+e.getMessage());
            return false;
        }
    }
    
    @Override
    public Boolean updateBook(Book book) {
        Connection con;
        try {
        con = DriverManager.getConnection(
            "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
        con.setAutoCommit(false);              
        updatePublisher(con,book.getPublisher(),book.getIsbn());
        PreparedStatement sta = con.prepareStatement(
                "UPDATE BOOKS SET TITLE = ?, PUBLISHER_ID = ?, PRICE = ?, AMOUNT = ? WHERE ISBN = ?");
        sta.setString(1, book.getTitle());
        sta.setInt(2, getPublishersIndex(con, book.getPublisher().getName()));
        sta.setDouble(3, book.getPrice());
        sta.setInt(4, book.getAmount());
        sta.setString(5, book.getIsbn());        
        sta.execute();        
        sta.close();
        updateAuthors(con,book.getAuthors(),book.getIsbn());
        con.commit();
        con.close();         
        return true;
        } catch (Exception e) {
            System.err.println("ExceptionUpdateBook: "+e.getMessage());
            return false;
        }
    }
    
    @Override
    public List<Book> getAllBooks(){
        Connection con;
        List<Book> books = new ArrayList<Book>();
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");            
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM BOOKS");
            ResultSet res = sta.executeQuery();
            while (res.next()) {
                String isbn = res.getString("ISBN");
                System.out.println(isbn);
                books.add(new Book(
                        res.getString("TITLE"),
                        isbn,
                        getPublisher(con,res.getInt("PUBLISHER_ID")),
                        getBookAuthors(con,isbn),
                        res.getInt("AMOUNT"),
                        res.getDouble("PRICE")
                        ));
            }
            res.close();
            sta.close();
            con.commit();
            con.close();   
            return books;
        } catch (Exception e) {
            System.err.println("ExceptionGetAllBooks: "+e.getMessage());
            return null;
        }
    }
    
    @Override
    public List<Author> getAllAuthors() {
        Connection con;
        List<Author> authors = new ArrayList<Author>();
        try{
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement("select * from authors");
            ResultSet res = sta.executeQuery();
            while(res.next()){
                authors.add(new Author(
                        res.getInt("ID"),
                        res.getString("NAME"),
                        res.getString("SURNAME")
                        ));
            }
            res.close();
            sta.close();
            con.commit();
            con.close();
            return authors;
        } catch(Exception e){
            System.out.println("ExceptionGetAllAuthors: "+e.getMessage());
            return authors;
        }
    }

    @Override
    public List<String> getAllPublishers() {
        Connection con;
        List<String> publishers = new ArrayList<String>();
        try{
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement("select * from authors");
            ResultSet res = sta.executeQuery();
            while(res.next()){
                publishers.add(res.getString("NAME"));
            }
            res.close();
            sta.close();
            con.commit();
            con.close();
            return publishers;
        } catch(Exception e){
            System.out.println("ExceptionGetAllAuthors: "+e.getMessage());
            return publishers;
        }
    }


    
    //BRANCHWS
    @Override
    public Order getOrder(int id) {
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM ORDERS WHERE ID = ?");            
            sta.setInt(1, id);            
            ResultSet res = sta.executeQuery();
            Order order = null;
            if(res.next()){
                int i = res.getInt("ID");
                order = new Order(
                        i,
                        getClient(res.getInt("CLIENT_ID")),
                        getOrderItems(con,i)
                        );
            } 
            res.close();            
            sta.close();
            con.commit();
            con.close();
            return order;
        } catch (Exception e) {
            System.err.println("ExceptionGetOrder: "+e.getMessage());
            return null;
        }
    }
    
    private List<OrderItem> getOrderItems(Connection con, int id) {
        List<OrderItem> oi = new ArrayList<OrderItem>();
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM ORDERBOOKS WHERE ORDER_ID = ?");            
            sta.setInt(1, id);            
            ResultSet res = sta.executeQuery(); 
            while(res.next()){
                Book book = getBook(res.getString("BOOK_ID"));
                book.setPrice(res.getInt("price"));
                oi.add(new OrderItem(
                        book, 
                        res.getInt("BOOK_AMOUNT"))
                        );
            } 
            res.close();            
            sta.close();
            return oi;
        } catch (Exception e) {
            System.err.println("ExceptionGetOrderItems: "+e.getMessage());
            return oi;
        }
    }
    
    private List<OrderItem> getOrderItems(Connection con, int id, String processed) {
        List<OrderItem> oi = new ArrayList<OrderItem>();
        try {
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM ORDERBOOKS WHERE ORDER_ID = ? AND PROCESSED = ?");            
            sta.setInt(1, id);                  
            sta.setString(2, processed);            
            ResultSet res = sta.executeQuery(); 
            while(res.next()){
                Book book = getBook(res.getString("BOOK_ID"));
                book.setPrice(res.getInt("price"));
                oi.add(new OrderItem(
                        book, 
                        res.getInt("BOOK_AMOUNT"))
                        );
            } 
            res.close();            
            sta.close();
            return oi;
        } catch (Exception e) {
            System.err.println("ExceptionGetOrderItemsWithProcessed: "+e.getMessage());
            return oi;
        }
    }

    private Boolean addOrderItems(Connection con, int oid, List<OrderItem> oi){
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = null;            
            for(int i = 0; i < oi.size(); i++){
                sta = con.prepareStatement(
                        "INSERT INTO ORDERBOOKS (ORDER_ID, BOOK_ID, BOOK_AMOUNT, PROCESSED,PRICE) VALUES (?, ?, ?, ?, ?)");
                sta.setInt(1, oid);
                sta.setString(2, oi.get(i).getBook().getIsbn());
                sta.setInt(3, oi.get(i).getQuantity());
                sta.setString(4, "NO");
                sta.setDouble(5, oi.get(i).getBook().getPrice());
                sta.execute();
            }            
            sta.close();
            con.commit();
            con.close();
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionAddOrderItems: "+e.getMessage());
            return false;
        }
    }
    
    @Override
    public int getFirstAvailableOrderIndex(){
        Connection con;
        int ans = 1;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                        "SELECT MAX(ID) AS MaxOrderID FROM Orders");            
            ResultSet res = sta.executeQuery();
            if(res.next()){
                ans = res.getInt("MaxOrderID")+1;
            }
            res.close();
            sta.close();
            con.commit();
            con.close();
            return ans;
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return -1;
        }
    }
    
    @Override
    public int getFirstAvailableClientsId(){
        Connection con;
        int ans = 1;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                        "SELECT MAX(ID) AS MaxClientsID FROM clients");            
            ResultSet res = sta.executeQuery();
            if(res.next()){
                ans = res.getInt("MaxClientsID")+1;
            }
            res.close();
            sta.close();
            con.commit();
            con.close();
            return ans;
        } catch (Exception e) {
            System.err.println("Exception: "+e.getMessage());
            return -1;
        }
    }
    
    @Override
    public Boolean addOrder(Order order) {
        Connection con;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta;
            if(order.getClient() != null){
                sta = con.prepareStatement(
                        "INSERT INTO ORDERS (ID, CLIENT_ID) VALUES (?, ?)");            
                sta.setInt(1, order.getId());
                int cid = order.getClient().getId();
                if(getNumberOfClientsOrders(con, cid) < 1) {
                    addClient(con, order.getClient());                    
                }
                sta.setInt(2, cid);
            } else {
                sta = con.prepareStatement(
                        "INSERT INTO ORDERS (ID, CLIENT_ID) VALUES (?, null)");            
                sta.setInt(1, order.getId());
            }
            sta.execute();            
            
            sta.close();
            if(!addOrderItems(con,order.getId(),order.getItems())){
                removeOnlyOrder(con,order.getId());
                if(getNumberOfClientsOrders(con, order.getId()) < 1){
                    removeClient(con, order.getClient().getId());                    
                }
                con.close();
                return false;
            }
            con.commit();
            con.close();
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionAddOrder: "+e.getMessage());
            return false;
        }
    }
    
    private Boolean removeClient(Connection con, int id) {
        try {
            PreparedStatement sta = con.prepareStatement(
                    "DELETE FROM clients WHERE ID = ?");
            sta.setInt(1, id);        
            sta.execute();
            sta.close();         
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveOnlyOrder: "+e.getMessage());
            return false;
        }
    }

    private Boolean removeOnlyOrder(Connection con, int id) {
        try {
            PreparedStatement sta = con.prepareStatement(
                    "DELETE FROM ORDERS WHERE ID = ?");
            sta.setInt(1, id);        
            sta.execute();
            sta.close();         
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveOnlyOrder: "+e.getMessage());
            return false;
        }
    }
    
    @Override
    public Boolean removeOrder(int id) {
        Connection con;
        try {
        con = DriverManager.getConnection(
            "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
        con.setAutoCommit(false);
        PreparedStatement sta = con.prepareStatement(
                "select * from orders where id = ?");
        sta.setInt(1, id);
        ResultSet res = sta.executeQuery();
        int cid = -1;
        if(res.next()){
            cid = res.getInt("CLIENT_ID");
        }
        
        sta = con.prepareStatement(
                "DELETE FROM ORDERS WHERE ID = ?");
        sta.setInt(1, id);        
        sta.execute();
        sta = con.prepareStatement(
                "DELETE FROM ORDERBOOKS WHERE ORDER_ID = ?");
        sta.setInt(1, id);        
        sta.execute();
        if(cid != -1){
            if(getNumberOfClientsOrders(con, cid)<1){
                sta = con.prepareStatement(
                        "DELETE FROM CLIENTS WHERE ID = ?");
                sta.setInt(1, cid);        
                sta.execute();            
            }
        }
        sta.close();
        con.commit();
        con.close();            
        return true;
        } catch (Exception e) {
            System.err.println("ExceptionRemoveOrder: "+e.getMessage());
            return false;
        }
    }
    
    private int getNumberOfClientsOrders(Connection con, int id){
        int ans=0;
        try{
            PreparedStatement sta = con.prepareStatement(
                    "select * from orders where client_id = ?");
            sta.setInt(1, id);
            ResultSet res = sta.executeQuery();
            while(res.next()){
                ans++;
            }
            res.close();
            sta.close();
            return ans;
        } catch(Exception e){
            System.out.println("ExceptionGetNumberOfClientsOrders"+e.getMessage());
            return -1;
        }
    }
    
    public Client getClient(int id){
        Connection con;
        Client client = null;
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM CLIENTS WHERE ID = ?");            
            sta.setInt(1, id);            
            ResultSet res = sta.executeQuery();            
            if(res.next()){
                client = new Client(
                        res.getInt("ID"),
                        res.getString("NAME"),
                        res.getString("SURNAME"),
                        res.getLong("PHONE"),
                        res.getString("EMAIL"));
            }
            res.close();            
            sta.close();
            con.commit();
            con.close();
            return client;
        } catch (Exception e) {
            System.err.println("ExceptionGetClient: "+e.getMessage());
            return null;
        }
    }
    
    private Boolean addClient(Connection con, Client client){
        try {
            PreparedStatement sta = con.prepareStatement(
                    "INSERT INTO CLIENTS (ID, NAME, SURNAME, EMAIL, PHONE) VALUES (?, ?, ?, ?, ?)");            
            sta.setInt(1, client.getId());
            sta.setString(2, client.getName());
            sta.setString(3, client.getSurname());
            sta.setString(4, client.getEmail());
            sta.setLong(5, client.getPhoneNumber());
            sta.execute();                        
            sta.close();
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionAddClient: "+e.getMessage());
            return false;
        }
    }
    
    @Override
    public List<Client> getAllClients() {
        Connection con;
        List<Client> clients = new ArrayList<Client>();
        try{
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement("select * from authors");
            ResultSet res = sta.executeQuery();
            while(res.next()){
                clients.add(new Client(
                        res.getInt("ID"),
                        res.getString("NAME"),
                        res.getString("SURNAME"),
                        res.getLong("PHONE"),
                        res.getString("EMAIL")   
                        ));
            }
            res.close();
            sta.close();
            con.commit();
            con.close();
            return clients;
        } catch(Exception e){
            System.out.println("ExceptionGetAllClients: "+e.getMessage());
            return clients;
        }
    }
        
    @Override
    public List<Order> getAllOrders(){
        Connection con;
        List<Order> orders = new ArrayList<Order>();
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM ORDERS");
            ResultSet res = sta.executeQuery();
            while (res.next()) {
                int i = res.getInt("ID");
                orders.add(new Order(
                        i,
                        getClient(res.getInt("CLIENT_ID")),
                        getOrderItems(con,i)
                        ));
            }
            res.close();
            sta.close();
            con.commit();
            con.close();   
            return orders;
        } catch (Exception e) {
            System.err.println("ExceptionGetAllOrders: "+e.getMessage());
            return null;
        }
    }

    @Override
    public List<Order> getOrders(String processed) {
        Connection con;
        List<Order> orders = new ArrayList<Order>();
        try {
            con = DriverManager.getConnection(
                "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            PreparedStatement sta = con.prepareStatement(
                    "SELECT * FROM ORDERS");
            ResultSet res = sta.executeQuery();
            while (res.next()) {
                int i = res.getInt("ID");
                List<OrderItem> ois = getOrderItems(con,i,processed);
                if(ois.size() > 0){
                    orders.add(new Order(
                            i,
                            getClient(res.getInt("CLIENT_ID")),
                            ois
                            ));
                    System.out.println("ordercreated");
                }
            }
            res.close();
            sta.close();
            con.commit();
            con.close();   
            return orders;
        } catch (Exception e) {
            System.err.println("ExceptiongetOrders: "+e.getMessage());
            return orders;
        }
    }

    
    
    
    //@WebServiceRef(wsdlLocation = "WEB-INF/wsdl/localhost_8080/BookstoreChainHQWS/BranchWebServiceWS.wsdl")
    //@WebServiceRef(wsdlLocation = "WEB-INF/wsdl/localhost_8080/BookstoreChainHQWS/BranchWebServiceWS.wsdl")
    private BranchWebServiceWS_Service service;
    
    @Override
    public Boolean processOrders(){
        System.out.println("ProcessOrdersStarts");
        try{
        service = new BranchWebServiceWS_Service();
        if(markProcessedOrderItems(service.getBranchWebServiceWSPort().processOrder(mapToHQOrder(getOrders("NO"))))){
            
            System.out.println("true");
            return true;
        } else {
            
            System.out.println("false :(");
            return false;
        }
        }
        catch(Exception e)
        {
            System.out.println("No connection :(");
            return false;
        }
    } 
    
    private hqclient.Order mapToHQOrder(List<Order> orders){        
        hqclient.Order hqo = new hqclient.Order();
        hqclient.OrderItem oi = new hqclient.OrderItem();
        System.out.println("mapping before fors with orders with size: "+orders.size());
        for(int i = 0; i < orders.size(); i++){
            for(int j = 0; j < orders.get(i).getOrderListSize(); j++){
                oi.setBook(mapToHQBook(orders.get(i).getItems().get(j).getBook()));
                oi.setAmount(orders.get(i).getItems().get(j).getQuantity());
                oi.setId(orders.get(i).getId());
                hqo.getOItems().add(oi);
            }
        }
        return hqo;
    }
    
    private hqclient.Book mapToHQBook(Book book){
        hqclient.Book b = new hqclient.Book();
        b.setIsbn(book.getIsbn());
        b.setTitle(book.getTitle());
        b.setAmount(book.getAmount());
        b.setPrice(book.getPrice());
        hqclient.Publisher pub = new hqclient.Publisher();
        pub.setName(book.getPublisher().getName());
        b.setPublisher(pub);
        hqclient.Author a = new hqclient.Author();
        for(int i = 0; i < book.getAuthors().size(); i++){
            a.setName(book.getAuthors().get(i).getName());
            a.setSurname(book.getAuthors().get(i).getSurname());
            b.getAuthors().add(a);
        }
        return b;
    }
    
    private Boolean markProcessedOrderItems(Response res){
        Connection con = null;
        PreparedStatement sta = null;
        try{
            con = DriverManager.getConnection(
                    "jdbc:derby://localhost:1527/branchdb;user=atuan;password=Black1");
            con.setAutoCommit(false);
            for (int i = 0; i < res.getRItems().size(); i++){
                sta = con.prepareStatement(
                        "UPDATE ORDERBOOKS SET PROCESSED = 'YES' WHERE ORDER_ID = ? AND BOOK_ID = ?");
                System.out.println("ID:"+res.getRItems().get(i).getId());
                sta.setInt(1, res.getRItems().get(i).getId());
                System.out.println("ID:"+res.getRItems().get(i).getBook().getIsbn());
                sta.setString(2, res.getRItems().get(i).getBook().getIsbn());
                sta.execute();
            }
            sta.close();
            con.commit();
            con.close();
            return true;
        } catch (Exception e) {
            System.err.println("ExceptionMPOI: "+e.getMessage());
            return false;
        }  
    }
}
